package dao.impl;

import dao.StudentDao;
import domain.Student;
import utils.JDBCUtils;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.sql.*;
import java.util.ArrayList;

public class StudentDaoImpl2 implements StudentDao {
    /**
     *
     * 查询所有
     * @return
     */
    @Override
    public ArrayList<Student> findAll() {
        ArrayList<Student> list = new ArrayList<>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 获取连接 注册驱动
            conn = JDBCUtils.getConnection();
            // 获取执行者对象
            stmt = conn.createStatement();
            // 执行sql语句 处理结果
            String sql = "select * from student";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                Integer sid = rs.getInt("sid");
                String name = rs.getString("name");
                Integer age = rs.getInt("age");
                Date rsDate = rs.getDate("birthday");
                // 封装对象
                Student student = new Student(sid, name, age, rsDate);
                // 把student对象保存到list中
                list.add(student);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭资源
            JDBCUtils.close(conn, stmt, rs);
        }


        return list;
    }

    /**
     * 根据id查询
     * @param id
     * @return
     */
    @Override
    public Student findById(Integer id) {
        Student stu = new Student();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            // 获取数据库连接 获取注册驱动
            conn = JDBCUtils.getConnection();
            // 获取执行者对象
            stmt = conn.createStatement();
            // 执行sql语句 查询结果
            String sql = " select * from student where sid ='" + stu.getSid() + "'";
            rs = stmt.executeQuery(sql);
            Integer sid = rs.getInt("sid");
            String name = rs.getString("name");
            Integer age = rs.getInt("age");
            Date date = rs.getDate("birthday");
            // 封装对象
            stu.setSid(sid);
            stu.setName(name);
            stu.setAge(age);
            stu.setBirthday(date);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(conn, stmt, rs);
        }
        return stu;
    }

    /**
     * 新增数据
     * @param student
     * @return
     */
    @Override
    public int insert(Student student) {
        Connection conn = null;
        Statement st = null;
        int result = 0;
        try {
            conn = JDBCUtils.getConnection();
            st = conn.createStatement();
            String sql = "insert into student id='" + student.getSid() + "' ";
            // 执行sql
            result = st.executeUpdate(sql);// 貌似是insert跟update这里要返回result
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 释放资源
            JDBCUtils.close(conn, st);
        }
        return result;
    }

    /**
     * 删除数据
     * @param id
     * @return
     */
    @Override
    public int delete(Integer id) {
        Connection conn = null;
        Statement stmt = null;
        Student stu = new Student();
        int result = 0;
        try {
            conn = JDBCUtils.getConnection();
            stmt = conn.createStatement();
            String sql = " delete from student where id='" + stu.getSid() + "'";
            result = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return result;
    }

    /**
     * 更新数据
     * @param student
     * @return
     */
    @Override
    public int update(Student student) {
        Connection conn = null;
        Statement stmt = null;
        int result = 0;
        try {
            conn = JDBCUtils.getConnection();
            stmt = conn.createStatement();
            Date date = student.getBirthday();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String sql = "update student set sid='" + student.getSid() + "',name='" + student.getName() + "', age='" + student.getAge() + "',brithday='" + date + "'";
            result = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtils.close(conn, stmt);
        }
        return result;
    }
}